import java.sql.*;
import java.util.*;
public class StudentOperationSystem
{
    static final String DB_URL = "jdbc:mysql://localhost:3306/MySQL"; //服务器URL
    static final String USER = "root";
    static final String PASSWORD = "hxf123456";
    private static boolean AnyName(String Info,Connection OBJServer) throws SQLException //判断OBJServer数据库中是否有StuName=Info的项
    {
        Statement statement=OBJServer.createStatement();
        String Command="select * from sakila.StudentList where StuName = '%s';";
        ResultSet QueryResult=statement.executeQuery(String.format(Command,Info));
        return QueryResult.next();
    }
    private static boolean AnyID(String Info,Connection OBJServer) throws SQLException //判断OBJServer数据库中是否有StuName=Info的项
    {
        Statement statement=OBJServer.createStatement();
        String Command="select * from sakila.StudentList where StuID = '%s';";
        ResultSet QueryResult=statement.executeQuery(String.format(Command,Info));
        return QueryResult.next();
    }
    private static Connection Connect() //获得连接类
    {
        Connection server=null;
        try //试图连接服务器
        {
            server=DriverManager.getConnection(DB_URL,USER,PASSWORD);
        }
        catch(SQLException e) //连接失败
        {
            System.out.println("服务器连接失败!错误原因:");
            e.printStackTrace();
            return null;
        }
        System.out.println("服务器连接成功!");
        return server;
    }
    private static void Disconnect(Connection OBJServer) //断开连接类
    {
        if(OBJServer!=null)
        {
            try //试图断开服务器
            {
                OBJServer.close();
            }
            catch(SQLException e)
            {
                System.out.println("数据保存失败!错误原因:");
                e.printStackTrace();
                return ;
            }
            System.out.println("数据保存成功!");
        }
        else System.out.println("该对象没有连接任何服务器!");
    }
    public static void Add(String[] StuInfo) //添加元素
    {
        Connection server=StudentOperationSystem.Connect();;//声连接服务器
        try
        {
            if(!server.isClosed()) System.out.println("成功访问数据库,正在尝试增加数据!");
            else
            {
                System.out.println("数据库打开失败!");
                return;
            }

            if(StudentOperationSystem.AnyID(StuInfo[0],server))
            {
                System.out.println("数据库中已有学号为"+StuInfo[0]+"的数据项!");
                return ;
            }

            if(StudentOperationSystem.AnyName(StuInfo[1],server))
            {
                System.out.println("数据库中已有名字为"+StuInfo[1]+"的数据项!");
                return ;
            }

            Statement statement = server.createStatement(); //创建语句
            String Command = "Insert into sakila.StudentList(StuID,StuName,Grade1,Grade2,Grade3) values('%s','%s','%s','%s','%s');"; //增加数据

            if(statement.executeUpdate(String.format(Command,StuInfo[0],StuInfo[1],StuInfo[2],StuInfo[3],StuInfo[4]))!=0)
            {
                System.out.println("插入成功!");
            }
            else
            {
                System.out.println("插入失败!");
            }
            statement.close();
            StudentOperationSystem.Disconnect(server);
        }
        catch (SQLException e)
        {
            System.out.println("增加数据失败!错误原因:");
            e.printStackTrace();
        }
    }
    public static void DeleteByName(String Info) //按名字删除元素
    {
        Connection server=StudentOperationSystem.Connect(); //连接服务器
        try
        {
            if(!server.isClosed()) System.out.println("成功访问数据库!正在尝试删除数据!");
            else
            {
                System.out.println("数据库打开失败!");
                return;
            }

            if(!StudentOperationSystem.AnyName(Info,server))
            {
                System.out.println("数据库中未找到任何名字为"+Info+"的数据项!");
                return ;
            }

            Statement statement = server.createStatement(); //创建语句
            String Command = "Delete from sakila.StudentList where StuName like '%s';";
            if(statement.executeUpdate(String.format(Command,Info))!=0)
            {
                System.out.println("删除成功!");
            }
            else
            {
                System.out.println("删除失败!");
            }
            statement.close();
            StudentOperationSystem.Disconnect(server);
        }
        catch (SQLException e)
        {
            System.out.println("删除数据失败!错误原因:");
            e.printStackTrace();
        }
    }
    public static void DeleteByID(String Info) //按学号删除元素
    {
        Connection server=StudentOperationSystem.Connect(); //连接服务器
        try
        {
            if(!server.isClosed()) System.out.println("成功访问数据库!正在尝试删除数据!");
            else
            {
                System.out.println("数据库打开失败!");
                return;
            }

            if(Info!="%"&&!StudentOperationSystem.AnyID(Info,server))
            {
                System.out.println("数据库中未找到任何学号为"+Info+"的数据项!");
                return ;
            }

            Statement statement = server.createStatement(); //创建语句
            String Command = "Delete from sakila.StudentList where StuID like '%s';";
            if(statement.executeUpdate(String.format(Command,Info))!=0)
            {
                System.out.println("删除成功!");
            }
            else
            {
                System.out.println("删除失败!");
            }
            statement.close();
            StudentOperationSystem.Disconnect(server);
        }
        catch (SQLException e)
        {
            System.out.println("删除数据失败!错误原因:");
            e.printStackTrace();
        }
    }
    public static void SearchByName(String Info) //按名字筛选元素
    {
        ArrayList<ArrayList<String>> Result=new ArrayList<ArrayList<String>>(); //寄存查询结果
        Connection server=StudentOperationSystem.Connect(); //连接服务器
        try
        {
            if(!server.isClosed()) System.out.println("成功访问数据库!正在尝试查找数据!");
            else
            {
                System.out.println("数据库打开失败!");
                return ;
            }
            Statement statement = server.createStatement(); //创建语句
            String Command = "select * from sakila.StudentList where StuName like '%s';";
            ResultSet QueryResult=statement.executeQuery(String.format(Command,Info));
            while(QueryResult.next())
            {
                ArrayList<String> item=new ArrayList<String>();
                int i;
                for(i=1;i<=5;++i)
                {
                    item.add(QueryResult.getString(i).trim());
                }
                Result.add(item);
            }
            statement.close();
            QueryResult.close();
            StudentOperationSystem.Disconnect(server);
            int i;
            System.out.println(String.format("%-12s%-12s%-12s%-12s%-12s","[ID]","[Name]","[Grade1]","[Grade2]","[Grade3]"));
            for(i=0;i<Result.size();++i)
            {
                System.out.println(String.format("%-12s%-12s%-12s%-12s%-12s",Result.get(i).get(0),Result.get(i).get(1),Result.get(i).get(2),Result.get(i).get(3),Result.get(i).get(4)));
            }
        }
        catch (SQLException e)
        {
            System.out.println("查找数据失败!错误原因:");
            e.printStackTrace();
        }
    }
    public static void SearchByID(String Info) //按学号筛选元素
    {
        ArrayList<ArrayList<String>> Result = new ArrayList<ArrayList<String>>(); //寄存查询结果
        Connection server = StudentOperationSystem.Connect(); //连接服务器
        try {
            if (!server.isClosed()) System.out.println("成功访问数据库!正在尝试查找数据!");
            else {
                System.out.println("数据库打开失败!");
                return;
            }
            Statement statement = server.createStatement(); //创建语句
            String Command = "select * from sakila.StudentList where StuID like '%s';";
            ResultSet QueryResult = statement.executeQuery(String.format(Command, Info));
            while (QueryResult.next()) {
                ArrayList<String> item = new ArrayList<String>();
                int i;
                for (i = 1; i <= 5; ++i) {
                    item.add(QueryResult.getString(i).trim());
                }
                Result.add(item);
            }
            statement.close();
            QueryResult.close();
            StudentOperationSystem.Disconnect(server);
            int i;
            System.out.println(String.format("%-12s%-12s%-12s%-12s%-12s", "[ID]", "[Name]", "[Grade1]", "[Grade2]", "[Grade3]"));
            for (i = 0; i < Result.size(); ++i) {
                System.out.println(String.format("%-12s%-12s%-12s%-12s%-12s", Result.get(i).get(0), Result.get(i).get(1), Result.get(i).get(2), Result.get(i).get(3), Result.get(i).get(4)));
            }
        } catch (SQLException e) {
            System.out.println("查找数据失败!错误原因:");
            e.printStackTrace();
        }

    }
    public static void AlterByName(String Info,int index,int value) //按名字修改元素
    {
        Connection server=StudentOperationSystem.Connect(); //连接服务器
        try
        {
            if(!server.isClosed()) System.out.println("成功访问数据库!正在尝试修改数据!");
            else
            {
                System.out.println("数据库打开失败!");
                return ;
            }

            if(!StudentOperationSystem.AnyName(Info,server))
            {
                System.out.println("数据库中未找到任何名字为"+Info+"的数据项!");
                return ;
            }

            Statement statement = server.createStatement(); //创建语句
            String Command = "Update sakila.StudentList set Grade%d = %d where StuName like '%s';";
            if(statement.executeUpdate(String.format(Command,index,value,Info))!=0)
            {
                System.out.println("修改成功!");
            }
            else
            {
                System.out.println("修改失败!");
            }
            statement.close();
            StudentOperationSystem.Disconnect(server);
        }
        catch (SQLException e)
        {
            System.out.println("修改数据失败!错误原因:");
            e.printStackTrace();
        }
    }
    public static void AlterByID(String Info,int index,int value) //按学号筛选元素
    {
        Connection server=StudentOperationSystem.Connect(); //连接服务器
        try
        {
            if(!server.isClosed()) System.out.println("成功访问数据库!正在尝试修改数据!");
            else
            {
                System.out.println("数据库打开失败!");
                return ;
            }

            if(!StudentOperationSystem.AnyID(Info,server))
            {
                System.out.println("数据库中未找到任何学号为"+Info+"的数据项!");
                return ;
            }

            Statement statement = server.createStatement(); //创建语句
            String Command = "Update sakila.StudentList set Grade%d = %d where StuID like '%s';";
            if(statement.executeUpdate(String.format(Command,index,value,Info))!=0)
            {
                System.out.println("修改成功!");
            }
            else
            {
                System.out.println("修改失败!");
            }
            statement.close();
            StudentOperationSystem.Disconnect(server);
        }
        catch (SQLException e)
        {
            System.out.println("修改数据失败!错误原因:");
            e.printStackTrace();
        }
    }
}
